﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text.RegularExpressions;


/// <summary>
/// Class to Access and Put Data as well as utilize various other Methods
/// </summary>
public class DataAccess
{
    #region Global Declarations and Constructor
    SqlConnection SQLConn = new SqlConnection();
    SqlConnection ExtSQLConn = new SqlConnection();
    System.Data.DataTable dtUsercodes = new System.Data.DataTable("Usercodes");
    System.Data.DataTable dtAllUsercodes = new System.Data.DataTable("AllUsercodes");
    System.Data.DataTable dtCallData = new System.Data.DataTable("CallData");
    System.Data.DataTable dtList = new System.Data.DataTable("List");
    System.Data.DataTable dtDNIS = new System.Data.DataTable("DNIS");
    System.Data.DataTable dtProducts = new System.Data.DataTable("Products");
    System.Data.DataTable dtProductsWhere = new System.Data.DataTable("ProductsWhere");
    System.Data.DataTable dtOrder = new System.Data.DataTable("Order");
    System.Data.DataTable dtOldCallData = new System.Data.DataTable("OldCallData");
    System.Data.DataTable dtLeadData = new System.Data.DataTable("LeadData");
    System.Data.DataTable dtPreviousCallData = new System.Data.DataTable("PreviousCallData");

    string LeadID = "0";
    string _DNIS;
    string _EnterpriseListID;
    string _CampaignName;
    string _Server;
    string _Project;


    public DataAccess()
    {
        //
        // TODO: Add constructor logic here
        //
        ConnectInbound();
    }


    public DataAccess(string s)
    {
        //
        // TODO: Add constructor logic here
        //
        ConnectOutbound();
    }



    public bool ConnectInbound()
    {
        string DNIS = HttpContext.Current.Session["dnis"].ToString();
        string EnterpriseListID = HttpContext.Current.Session["listid"].ToString();
        string CampaignName = HttpContext.Current.Session["campaignname"].ToString();
        string Server = HttpContext.Current.Session["servername"].ToString();
        string Project = HttpContext.Current.Session["projectname"].ToString();

        if (HttpContext.Current.Session["leadid"] != null)
        {
            LeadID = HttpContext.Current.Session["leadid"].ToString();
        }
        
        _DNIS = DNIS;
        _EnterpriseListID = EnterpriseListID;
        _CampaignName = CampaignName;
        _Server = Server;
        _Project = Project;
        if (Server == "65.100.157.132")
        {
            SQLConn.ConnectionString = "Data Source=" + Server + "; Initial Catalog=" + Project + "; User ID = webuser; Password = W3bscript$";
            ExtSQLConn.ConnectionString = "Data Source=65.100.157.132; Initial Catalog=SharedPool; User ID = webuser; Password = W3bscript$";
        }
        else
        {
            SQLConn.ConnectionString = "Data Source=" + Server + "; Initial Catalog=" + Project + "; User ID = portaluser; Password = sqluser";
            ExtSQLConn.ConnectionString = "Data Source=65.100.157.132; Initial Catalog=SharedPool; User ID = webuser; Password = W3bscript$";
        }


        SQLConn.Open();
        if (SQLConn.State == System.Data.ConnectionState.Open)
        {
            //Insert CallData record
            string SQLInsertCallData = "EXEC [f_InsertIncontactCallData] '" + EnterpriseListID + "', '" + DNIS + "', '" + HttpContext.Current.Request.QueryString["agentid"].ToString() + "', '" + HttpContext.Current.Request.QueryString["ani"] + "' ";
            //string SQLInsertCallData = "EXEC [f_InsertIncontactCallData] '" + EnterpriseListID + "', '" + DNIS + "', '" + HttpContext.Current.Session["agentid"].ToString() + "', '' ";
            SqlCommand SqlCmdInsertCallData = new SqlCommand(SQLInsertCallData, SQLConn);
            SqlCmdInsertCallData.CommandType = System.Data.CommandType.Text;
            SqlCmdInsertCallData.ExecuteNonQuery();

            //Fill dtUsercodes Table
            string SQLUserCodes = "EXEC [f_GetUserCodesForAgent] '" + CampaignName + "' ";
            SqlCommand SqlCmdUsercodes = new SqlCommand(SQLUserCodes, SQLConn);
            SqlCmdUsercodes.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterUsercodes = new SqlDataAdapter(SqlCmdUsercodes);
            dataAdapterUsercodes.Fill(dtUsercodes);
            if (LeadID == "0" || String.IsNullOrEmpty(LeadID)) //If it is an inbound call then no worries just continue as normal
            {
                //Fill dtCallData Table
                string SQLCallData = "Select * from " + CampaignName + "_CallData (nolock) where enterpriselistid = " + EnterpriseListID + " ";
                SqlCommand SqlCmdCallData = new SqlCommand(SQLCallData, SQLConn);
                SqlCmdCallData.CommandType = System.Data.CommandType.Text;
                SqlDataAdapter dataAdapterCallData = new SqlDataAdapter(SqlCmdCallData);
                dataAdapterCallData.Fill(dtCallData);
            }
            else  //if it is an outbound call get the old call data record to populate fields with.
            {
                //Fill dtCallData Table
                string SQLCallData = "Select * from " + CampaignName + "_CallData (nolock) where enterpriselistid = " + EnterpriseListID + " ";
                SqlCommand SqlCmdCallData = new SqlCommand(SQLCallData, SQLConn);
                SqlCmdCallData.CommandType = System.Data.CommandType.Text;
                SqlDataAdapter dataAdapterCallData = new SqlDataAdapter(SqlCmdCallData);
                dataAdapterCallData.Fill(dtCallData);

                //Fill dtOldCallData Table
                string SQLOldCallData = "Select * from " + CampaignName + "_CallData (nolock) where enterpriselistid in (select ContactID from [65.100.157.132].SharedPool.dbo.OBLeads where LeadID =" + LeadID + ") ";
                SqlCommand SqlCmdOldCallData = new SqlCommand(SQLOldCallData, SQLConn);
                SqlCmdOldCallData.CommandType = System.Data.CommandType.Text;
                SqlDataAdapter dataAdapterOldCallData = new SqlDataAdapter(SqlCmdOldCallData);
                dataAdapterOldCallData.Fill(dtOldCallData);

                //Fill Leads Table
                //string SQLLeadData = "Select * from " + CampaignName + "_LeadData (nolock) where LeadID = " + LeadID + " " ;
                //SqlCommand SqlCmdLeadData = new SqlCommand(SQLLeadData, SQLConn);
                //SqlCmdLeadData.CommandType = System.Data.CommandType.Text;
                //SqlDataAdapter dataAdapterLeadData = new SqlDataAdapter(SqlCmdLeadData);
                //dataAdapterLeadData.Fill(dtLeadData);

            }

            //Fill dtDNIS Table
            string SQLDNIS = "EXEC [f_GetDNISInfo] '" + DNIS + "' ";
            SqlCommand SqlCmdDNIS = new SqlCommand(SQLDNIS, SQLConn);
            SqlCmdDNIS.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterDNIS = new SqlDataAdapter(SqlCmdDNIS);
            dataAdapterDNIS.Fill(dtDNIS);

            //Fill dtProducts Table
            string SQLProducts = "EXEC [f_GetProducts] '" + _CampaignName + "' ";
            SqlCommand SqlCmdProducts = new SqlCommand(SQLProducts, SQLConn);
            SqlCmdProducts.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterProducts = new SqlDataAdapter(SqlCmdProducts);
            dataAdapterProducts.Fill(dtProducts);

            //Create Order table
            dtOrder.Columns.Add("OrderID");
            dtOrder.Columns.Add("enterpriselistid");
            dtOrder.Columns.Add("CampaignID");
            dtOrder.Columns.Add("ProductID");
            dtOrder.Columns.Add("ProductTypeID");
            dtOrder.Columns.Add("ProductDesc");
            dtOrder.Columns.Add("BaseRevenue");
            dtOrder.Columns.Add("SecondaryRevenue");
            dtOrder.Columns.Add("Quantity");

            //Replicate the structure of dtProducts into dtProductsWhere
            dtProductsWhere = dtProducts.Clone();


            if (dtUsercodes.Rows.Count > 0 && dtCallData.Rows.Count > 0 && dtDNIS.Rows.Count > 0)
            {
                SQLConn.Close();
                return true;
            }
            else
            {
                SQLConn.Close();
                return false;
            }

        }
        else
            return false;
    }


    public bool ConnectOutbound()
    {
        string EnterpriseListID = HttpContext.Current.Session["listid"].ToString();
        string CampaignName = HttpContext.Current.Session["campaignname"].ToString();
        string Server = HttpContext.Current.Session["servername"].ToString();
        string Project = HttpContext.Current.Session["projectname"].ToString();

        _EnterpriseListID = EnterpriseListID;
        _CampaignName = CampaignName;
        _Server = Server;
        _Project = Project;
        if (Server == "65.100.157.132")
        {
            SQLConn.ConnectionString = "Data Source=" + Server + "; Initial Catalog=" + Project + "; User ID = webuser; Password = W3bscript$";
        }
        else
        {
            SQLConn.ConnectionString = "Data Source=" + Server + "; Initial Catalog=" + Project + "; User ID = portaluser; Password = sqluser";
        }


        SQLConn.Open();
        if (SQLConn.State == System.Data.ConnectionState.Open)
        {
            //Insert CallData record
            string SQLInsertCallData = "EXEC [f_InsertIncontactCallData] '" + EnterpriseListID + "', '', '', '" + HttpContext.Current.Request.QueryString["ani"] + "' ";
            SqlCommand SqlCmdInsertCallData = new SqlCommand(SQLInsertCallData, SQLConn);
            SqlCmdInsertCallData.CommandType = System.Data.CommandType.Text;
            SqlCmdInsertCallData.ExecuteNonQuery();

            //Fill dtUsercodes Table
            string SQLUserCodes = "EXEC [f_GetUserCodesForAgent] '" + CampaignName + "' ";
            SqlCommand SqlCmdUsercodes = new SqlCommand(SQLUserCodes, SQLConn);
            SqlCmdUsercodes.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterUsercodes = new SqlDataAdapter(SqlCmdUsercodes);
            dataAdapterUsercodes.Fill(dtUsercodes);

            //Fill dtAllUsercodes Table
            string SQLAllUserCodes = "EXEC [f_GetUserCodes] '" + CampaignName + "' ";
            SqlCommand SqlCmdAllUsercodes = new SqlCommand(SQLAllUserCodes, SQLConn);
            SqlCmdAllUsercodes.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterAllUsercodes = new SqlDataAdapter(SqlCmdAllUsercodes);
            dataAdapterAllUsercodes.Fill(dtAllUsercodes);

            //Fill dtCallData Table
            string SQLCallData = "Select * from " + CampaignName + "_CallData (nolock) where enterpriselistid = " + EnterpriseListID + " ";
            SqlCommand SqlCmdCallData = new SqlCommand(SQLCallData, SQLConn);
            SqlCmdCallData.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterCallData = new SqlDataAdapter(SqlCmdCallData);
            dataAdapterCallData.Fill(dtCallData);

            //Fill dtList Table
            string SQLList = "Select * from list (nolock) where enterpriselistid = " + EnterpriseListID + " ";
            SqlCommand SqlCmdList = new SqlCommand(SQLList, SQLConn);
            SqlCmdList.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterList = new SqlDataAdapter(SqlCmdList);
            dataAdapterList.Fill(dtList);

            //Fill dtProducts Table
            string SQLProducts = "EXEC [f_GetProducts] '" + _CampaignName + "' ";
            SqlCommand SqlCmdProducts = new SqlCommand(SQLProducts, SQLConn);
            SqlCmdProducts.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterProducts = new SqlDataAdapter(SqlCmdProducts);
            dataAdapterProducts.Fill(dtProducts);

            //Create Order table
            dtOrder.Columns.Add("OrderID");
            dtOrder.Columns.Add("enterpriselistid");
            dtOrder.Columns.Add("CampaignID");
            dtOrder.Columns.Add("ProductID");
            dtOrder.Columns.Add("ProductTypeID");
            dtOrder.Columns.Add("ProductDesc");
            dtOrder.Columns.Add("BaseRevenue");
            dtOrder.Columns.Add("SecondaryRevenue");
            dtOrder.Columns.Add("Quantity");

            //Replicate the structure of dtProducts into dtProductsWhere
            dtProductsWhere = dtProducts.Clone();


            if (dtUsercodes.Rows.Count > 0 && dtCallData.Rows.Count > 0 && dtDNIS.Rows.Count > 0)
            {
                SQLConn.Close();
                return true;
            }
            else
            {
                SQLConn.Close();
                return false;
            }

        }
        else
            return false;
    }
    #endregion Global Declarations and Constructor

    #region Encryption
    public string EnryptData(string str)
    {
        com.focusservices.fast.EncryptData FAST = new com.focusservices.fast.EncryptData();
        return FAST.CallEncryptData(str);
    }


    public string DecryptData(string str)
    {
        com.focusservices.fast.EncryptData FAST = new com.focusservices.fast.EncryptData();
        return FAST.DecryptData(str, "AF7CD-AAXZY-32acTQZ-ZZIV4R9QZ");
    }
    #endregion Encryption

    #region Get Functions GetDNIS, GetUsercodes, GetProducts (3 overloads)
    //get information from the in-memory tables
    public string GetDNIS(string ColumnName)
    {
        return Convert.ToString(dtDNIS.Rows[0][ColumnName]);
    }

    public System.Data.DataTable GetUsercodes()
    {
        return dtUsercodes;
    }

    public System.Data.DataTable GetAllUsercodes()
    {
        return dtAllUsercodes;
    }

    public System.Data.DataTable GetProducts()
    {
        return dtProducts;
    }


    public System.Data.DataTable GetProducts(string Where)  //This GetProducts Overload Actually accesses the Database and should be used sparingly
    {
        //Fill dtProductsWhere Table
        dtProductsWhere.Rows.Clear();
        string SQLProductsWhere = "Select * from VoiceNet_Focus..WebProducts with (nolock) where CampaignName = '" + _CampaignName + "' AND " + Where + " ";
        SqlCommand SqlCmdProductsWhere = new SqlCommand(SQLProductsWhere, SQLConn);
        SqlCmdProductsWhere.CommandType = System.Data.CommandType.Text;
        SqlDataAdapter dataAdapterProductsWhere = new SqlDataAdapter(SqlCmdProductsWhere);
        dataAdapterProductsWhere.Fill(dtProductsWhere);
        return dtProductsWhere;
    }



    public System.Data.DataTable GetProducts(string ColumnName, string EqualsValue)
    {
        dtProductsWhere.Rows.Clear();
        int NumberOfColumns = dtProducts.Columns.Count;
        for (int i = 0; i < dtProducts.Rows.Count; i++)
        {
            if (dtProducts.Rows[i][ColumnName].ToString() == EqualsValue)
            {
                dtProductsWhere.ImportRow(dtProducts.Rows[i]);
            }
        }
        return dtProductsWhere;
    }
    # endregion Get Functions

    #region Order Functions: PutOrderItem, RemoveOrderItem (2 overloads), GetOrder, GetOrderTotal, CommitOrder
    public void PutOrderItem(int ProductID, int Quantity)
    {
        if (ProductID != -1)
        {
            System.Data.DataRow workRow = dtOrder.NewRow();
            for (int i = 0; i < dtProducts.Rows.Count; i++)
            {
                if (dtProducts.Rows[i]["ProductID"].ToString() == ProductID.ToString())
                {
                    workRow["CampaignID"] = dtProducts.Rows[i]["FastCampaignID"];
                    workRow["ProductTypeID"] = dtProducts.Rows[i]["ProductTypeID"];
                    workRow["ProductDesc"] = dtProducts.Rows[i]["ProductDesc"];
                    workRow["BaseRevenue"] = dtProducts.Rows[i]["BaseRevenue"];
                    workRow["SecondaryRevenue"] = dtProducts.Rows[i]["OtherRevenue"];
                    workRow["ProductID"] = dtProducts.Rows[i]["ProductID"];
                }
            }


            workRow["enterpriselistid"] = _EnterpriseListID.ToString();
            workRow["Quantity"] = Quantity;
            dtOrder.Rows.Add(workRow);
        }

    }

    public void RemoveOrderItem(int ProductID, int Quantity)
    {
        bool exit = false;
        for (int i = 0; i < dtOrder.Rows.Count; i++)
        {
            if (exit == false)
            {
                if (System.Convert.ToInt32(dtOrder.Rows[i]["ProductID"]) == ProductID && System.Convert.ToInt32(dtOrder.Rows[i]["Quantity"]) == Quantity)
                {
                    dtOrder.Rows.RemoveAt(i);
                    exit = true;
                }
            }
        }
    }

    public void RemoveOrderItem(int ProductID)
    {
        bool exit = false;
        for (int i = 0; i < dtOrder.Rows.Count; i++)
        {
            if (exit == false)
            {
                if (System.Convert.ToInt32(dtOrder.Rows[i]["ProductID"]) == ProductID)
                {
                    dtOrder.Rows.RemoveAt(i);
                    exit = true;
                }
            }
        }
    }

    public System.Data.DataTable GetOrder()
    {
        return dtOrder;
    }

    public double GetOrderTotal()
    {
        double OrderTotal = 0.00;
        for (int i = 0; i < dtOrder.Rows.Count; i++)
        {
            OrderTotal = OrderTotal + Convert.ToDouble((Convert.ToDouble(dtOrder.Rows[i]["BaseRevenue"]) * Convert.ToDouble(dtOrder.Rows[i]["Quantity"])) + Convert.ToDouble(dtOrder.Rows[i]["SecondaryRevenue"]));
        }
        return OrderTotal;
    }

    public void CommitOrder()
    {

        SQLConn.Open();

        string SQLOrderDelete = "Delete from Orders where enterpriselistid = " + _EnterpriseListID;

        SqlCommand SqlCmdOrderDelete = new SqlCommand(SQLOrderDelete, SQLConn);
        SqlCmdOrderDelete.CommandType = System.Data.CommandType.Text;
        SqlCmdOrderDelete.ExecuteNonQuery();

        //SQLConn.Close();


        //SQLConn.Open();
        for (int i = 0; i < dtOrder.Rows.Count; i++)
        {
            int NumberOfColumns = dtOrder.Columns.Count;
            string column = "";
            string SetStatement = "";
            string value = "";
            for (int index = 1; index < NumberOfColumns; index++)
            {

                column = dtOrder.Columns[index].ColumnName.ToString();
                value = dtOrder.Rows[i][column].ToString();
                SetStatement = SetStatement + " '" + value + "',";


            }
            SetStatement = SetStatement.TrimEnd(',');
            string SQLOrderInsert;
            //if (_Server == "65.100.157.132")
            //{
            //    SQLOrderInsert = "INSERT INTO Orders (enterpriselistid, CampaignID, ProductID, ProductTypeID, ProductDesc, BaseRevenue, SecondaryRevenue, Quantity, project) VALUES (" + SetStatement + ", 'SharedPool') ";
            //}
            //else
            //{
                SQLOrderInsert = "INSERT INTO Orders (enterpriselistid, CampaignID, ProductID, ProductTypeID, ProductDesc, BaseRevenue, SecondaryRevenue, Quantity) VALUES (" + SetStatement + ") ";
            //}

            SqlCommand SqlCmdOrderUpdate = new SqlCommand(SQLOrderInsert, SQLConn);
            SqlCmdOrderUpdate.CommandType = System.Data.CommandType.Text;
            SqlCmdOrderUpdate.ExecuteNonQuery();
        }
        SQLConn.Close();
    }
    #endregion Order Functions

    #region _CallData Functions: GetCallData, PutCallData (2 overloads), CommitCallData

    public string GetCallData(string ColumnName)
    {
        return Convert.ToString(dtCallData.Rows[0][ColumnName]);
    }
    public string OBGetOldCallData(string ColumnName)
    {
        return Convert.ToString(dtOldCallData.Rows[0][ColumnName]);
    }
    public string OBGetLeadData(string ColumnName)
    {
        return Convert.ToString(dtLeadData.Rows[0][ColumnName]);
    }
    public string OBGetPreviousCallData(string ColumnName)
    {
        return Convert.ToString(dtPreviousCallData.Rows[0][ColumnName]);
    }

    // Overload of PutCalldata to include 3 parameters including datatype which converts the datatype (used in ActiveYellow and Allu)
    public void PutCallData(string ColumnName, string datatype, string Data)
    {
        switch (datatype)
        {
            case "string":
                dtCallData.Rows[0][ColumnName] = Data;
                break;
            case "int":
                dtCallData.Rows[0][ColumnName] = Convert.ToInt32(Data);
                break;
            case "bool":
                dtCallData.Rows[0][ColumnName] = Convert.ToBoolean(Data);
                break;
            default:
                dtCallData.Rows[0][ColumnName] = Data;
                break;
        }



    }

    //Overload of PutCallData to include just ColumnName and Data, but NOT datatype
    public void PutCallData(string ColumnName, string Data)
    {
        dtCallData.Rows[0][ColumnName] = Data;
    }

    //public void CommitCallData()
    //{
    //    int NumberOfColumns = dtCallData.Columns.Count;
    //    string column;
    //    string SetStatement = "";
    //    string value;
    //    for (int index = 0; index < NumberOfColumns; index++)
    //    {

    //        column = dtCallData.Columns[index].ColumnName.ToString();
    //        value = dtCallData.Rows[0][column].ToString();
    //        SetStatement = SetStatement + " " + column + " = '" + value + "',";

    //    }
    //    SetStatement = SetStatement.TrimEnd(',');
    //    string SQLCallDataUpdate = "UPDATE " + _CampaignName + "_CallData SET " + SetStatement + " WHERE enterpriselistid = '" + _EnterpriseListID + "' ";
    //    SQLConn.Open();
    //    SqlCommand SqlCmdCallDataUpdate = new SqlCommand(SQLCallDataUpdate, SQLConn);
    //    SqlCmdCallDataUpdate.CommandType = System.Data.CommandType.Text;
    //    SqlCmdCallDataUpdate.ExecuteNonQuery();
    //    SQLConn.Close();

    //}

    public void CommitCallData()
    {
        int NumberOfColumns = dtCallData.Columns.Count;
        string column;
        string SetStatement = "";
        string value;
        SQLConn.Open();
        string SQLCallDataUpdate;
        SqlCommand SqlCmdCallDataUpdate = new SqlCommand();
        SqlCmdCallDataUpdate.Connection = SQLConn;

        for (int index = 0; index < NumberOfColumns; index++)
        {

            column = dtCallData.Columns[index].ColumnName.ToString();
            value = dtCallData.Rows[0][column].ToString();
            SetStatement = SetStatement + " " + column + " = @" + column + ",";
            SqlCmdCallDataUpdate.Parameters.Add("@" + column, System.Data.SqlDbType.VarChar);
            SqlCmdCallDataUpdate.Parameters["@" + column].Value = value;
        }
        SetStatement = SetStatement.TrimEnd(',');
        SQLCallDataUpdate = "UPDATE " + _CampaignName + "_CallData SET " + SetStatement + " WHERE enterpriselistid = '" + _EnterpriseListID + "' ";
        SqlCmdCallDataUpdate.CommandText = SQLCallDataUpdate;
        SqlCmdCallDataUpdate.CommandType = System.Data.CommandType.Text;

        SqlCmdCallDataUpdate.ExecuteNonQuery();
        SQLConn.Close();
    }

    public void FinalizeOBLead()
    {
        if (dtDNIS.Rows[0]["SubGroup1"].ToString() == "Outbound") // does dnis subgroup1 equal Outbound
        {
            foreach (System.Data.DataRow dr in dtUsercodes.Rows) // iterate through usercodes to find matching usercode
            {
                if (dr["usercode"].ToString() == dtCallData.Rows[0]["webusercode"].ToString()) // once match is found, check if it's disposition != SF, if so, finalize the lead.
                {
                    if (dr["disposition"].ToString() != "SF")
                    {
                        //finalize lead here
                        SQLConn.Open();
                        string SQLCallDataUpdate;
                        SqlCommand SqlCmdCallDataUpdate = new SqlCommand();
                        SqlCmdCallDataUpdate.Connection = SQLConn;

                        //SQLCallDataUpdate = "EXEC [65.100.157.132].[SharedPool].[dbo].[f_OB_FinalizeLead] '" + HttpContext.Current.Request.QueryString["ani"] + "' ";
                        SQLCallDataUpdate = "update [65.100.157.132].[SharedPool].[dbo].[OBLeads]" +
                                            "set finalized = 1, usercode = '" + dtCallData.Rows[0]["webusercode"].ToString() + "' " + // Updated by Johanna on 2/7/2012
                                            "where phone = '" + HttpContext.Current.Session["ani"].ToString() + "' ";
                        SqlCmdCallDataUpdate.CommandText = SQLCallDataUpdate;
                        SqlCmdCallDataUpdate.CommandType = System.Data.CommandType.Text;

                        SqlCmdCallDataUpdate.ExecuteNonQuery();
                        SQLConn.Close();
                    }

                    break;
                }
            }
        }

        //int NumberOfColumns = dtCallData.Columns.Count;
        //string column;
        //string SetStatement = "";
        //string value;
        //SQLConn.Open();
        //string SQLCallDataUpdate;
        //SqlCommand SqlCmdCallDataUpdate = new SqlCommand();
        //SqlCmdCallDataUpdate.Connection = SQLConn;

        //for (int index = 0; index < NumberOfColumns; index++)
        //{

        //    column = dtCallData.Columns[index].ColumnName.ToString();
        //    value = dtCallData.Rows[0][column].ToString();
        //    SetStatement = SetStatement + " " + column + " = @" + column + ",";
        //    SqlCmdCallDataUpdate.Parameters.Add("@" + column, System.Data.SqlDbType.VarChar);
        //    SqlCmdCallDataUpdate.Parameters["@" + column].Value = value;
        //}
        //SetStatement = SetStatement.TrimEnd(',');
        //SQLCallDataUpdate = "UPDATE SharedPool..OBLeads SET " + SetStatement + " WHERE enterpriselistid = '" + _EnterpriseListID + "' ";
        //SqlCmdCallDataUpdate.CommandText = SQLCallDataUpdate;
        //SqlCmdCallDataUpdate.CommandType = System.Data.CommandType.Text;

        //SqlCmdCallDataUpdate.ExecuteNonQuery();
        //SQLConn.Close();

    }
    #endregion _CallData Functions

    #region OutBound Functions: OBLeadInsert (2 overloads), OBFinalizeLead

    public string GetList(string ColumnName)
    {
         return Convert.ToString(dtList.Rows[0][ColumnName]);
    }

    public bool OBLeadInsert(int ContactID, string CallBackDate, string Phone, string CallerID_DNIS)
    {
        try
        {
            
            ExtSQLConn.Open();
            string SQLInsertLead;
            SqlCommand SqlCmdInsertLead = new SqlCommand();
            SqlCmdInsertLead.Connection = ExtSQLConn;
            SQLInsertLead = "Exec f_OB_InsertLead " + ContactID + ", '" + CallBackDate + "', '" + Phone + "', '" + CallerID_DNIS + "'";
            SqlCmdInsertLead.CommandText = SQLInsertLead;
            SqlCmdInsertLead.CommandType = System.Data.CommandType.Text;
            SqlCmdInsertLead.ExecuteNonQuery();
            ExtSQLConn.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }

    public bool OBLeadInsert(string CallBackDate, string Phone, string CallerID_DNIS)
    {
        try
        {
            ExtSQLConn.Open();
            string SQLInsertLead;
            SqlCommand SqlCmdInsertLead = new SqlCommand();
            SqlCmdInsertLead.Connection = ExtSQLConn;
            SQLInsertLead = "Exec f_OB_InsertLead null, '" + CallBackDate + "', '" + Phone + "', '" + CallerID_DNIS + "'";
            SqlCmdInsertLead.CommandText = SQLInsertLead;
            SqlCmdInsertLead.CommandType = System.Data.CommandType.Text;
            SqlCmdInsertLead.ExecuteNonQuery();
            ExtSQLConn.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }

    public bool OBLeadRemove(int ContactID)
    {
        try
        {
            ExtSQLConn.Open();
            string SQLInsertLead;
            SqlCommand SqlCmdInsertLead = new SqlCommand();
            SqlCmdInsertLead.Connection = ExtSQLConn;
            SQLInsertLead = "Delete from [SharedPool].[dbo].[OBLeads] where ContactID = " + ContactID.ToString() + "" ;
            SqlCmdInsertLead.CommandText = SQLInsertLead;
            SqlCmdInsertLead.CommandType = System.Data.CommandType.Text;
            SqlCmdInsertLead.ExecuteNonQuery();
            ExtSQLConn.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }

    public bool OBFinalizeLead(string Usercode)
    {
        try
        {
            ExtSQLConn.Open();
            string SQLFinalizeLead;
            SqlCommand SqlCmdFinalizeLead = new SqlCommand();
            SqlCmdFinalizeLead.Connection = ExtSQLConn;
            SQLFinalizeLead = "update [SharedPool].[dbo].[OBLeads] " +
                                            "set finalized = 1,  Usercode = '" + Usercode + "' " +
                                            "where LeadID = " + LeadID + " ";
            SqlCmdFinalizeLead.CommandText = SQLFinalizeLead;
            SqlCmdFinalizeLead.CommandType = System.Data.CommandType.Text;
            SqlCmdFinalizeLead.ExecuteNonQuery();
            ExtSQLConn.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }

    public bool OBUpdateLead(int ContactID, string CallBackDateTime, string Phone, string Usercode)
    {
        try
        {
            ExtSQLConn.Open();
            string SQLFinalizeLead;
            SqlCommand SqlCmdFinalizeLead = new SqlCommand();
            SqlCmdFinalizeLead.Connection = ExtSQLConn;
            SQLFinalizeLead = "update [SharedPool].[dbo].[OBLeads] " +
                                            "set ContactID = " + ContactID + ", CallbackTime = '" + CallBackDateTime + "', Phone = '" + Phone + "', Usercode = '" + Usercode + "' " +
                                            "where LeadID = " + LeadID + " ";
            SqlCmdFinalizeLead.CommandText = SQLFinalizeLead;
            SqlCmdFinalizeLead.CommandType = System.Data.CommandType.Text;
            SqlCmdFinalizeLead.ExecuteNonQuery();
            ExtSQLConn.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }
    #endregion Outbound Functions

    #region Validation Functions: IsValidEmail, ValidateRoutingNumber, IsValidCC

    public bool IsValidEmail(string Email)
    {
        // Return true if strIn is in valid e-mail format.
        return Regex.IsMatch(Email,
               @"^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))" +
               @"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$");
    }

    //Enumerator for Card Types
    public enum CardType
    { MasterCard, BankCard, Visa, AmericanExpress, Discover, DinersClub, JCB };

    //Validate a Credit Card Number
    public bool IsValidCC(CardType cardType, string cardNumber)
    {
        byte[] number = new byte[16]; // number to validate

        // Remove non-digits
        int len = 0;
        for (int i = 0; i < cardNumber.Length; i++)
        {
            if (char.IsDigit(cardNumber, i))
            {
                if (len == 16) return false; // number has too many digits
                number[len++] = byte.Parse(cardNumber[i].ToString()); //number[len++] = byte.Parse(cardNumber[i]);
            }
        }

        // Validate based on card type, first if tests length, second tests prefix
        switch (cardType)
        {
            case CardType.MasterCard:
                if (len != 16)
                    return false;
                if (number[0] != 5 || number[1] == 0 || number[1] > 5)
                    return false;
                break;

            case CardType.BankCard:
                if (len != 16)
                    return false;
                if (number[0] != 5 || number[1] != 6 || number[2] > 1)
                    return false;
                break;

            case CardType.Visa:
                if (len != 16 && len != 13)
                    return false;
                if (number[0] != 4)
                    return false;
                break;

            case CardType.AmericanExpress:
                if (len != 15)
                    return false;
                if (number[0] != 3 || (number[1] != 4 && number[1] != 7))
                    return false;
                break;

            case CardType.Discover:
                if (len != 16)
                    return false;
                if (number[0] != 6 || number[1] != 0 || number[2] != 1 || number[3] != 1)
                    return false;
                break;

            case CardType.DinersClub:
                if (len != 14)
                    return false;
                if (number[0] != 3 || (number[1] != 0 && number[1] != 6 && number[1] != 8) || number[1] == 0 && number[2] > 5)
                    return false;
                break;

            case CardType.JCB:
                if (len != 16 && len != 15)
                    return false;
                if (number[0] != 3 || number[1] != 5)
                    return false;
                break;
        }

        // Use Luhn Algorithm to validate
        int sum = 0;
        for (int i = len - 1; i >= 0; i--)
        {
            if (i % 2 == len % 2)
            {
                int n = number[i] * 2;
                sum += (n / 10) + (n % 10);
            }
            else
                sum += number[i];
        }
        return (sum % 10 == 0);
    }



    /// Validates the routing number.
    ///
    public bool ValidateRoutingNumber(int routingNumber)
    {
        //The check equation for a number a1a2a3a4a5a6a7a8a9 is 3a1 + 7a2 + a3 + 3a4 + 7a5 + a6 + 3a7 + 7 a8 + a9 mod 10 = 0

        //rewrote for higher efficiency

        //must be 9 digits
        if (routingNumber < 100000000)
            return false;
        if (routingNumber > 999999999)
            return false;
        //build array for quick comparison
        //left to right using fun with math.

        int[] routingNumberAsArray =
        {
            routingNumber / 100000000,
            (routingNumber % 100000000) / 10000000,
            (routingNumber % 10000000) / 1000000,
            (routingNumber % 1000000) / 100000,
            (routingNumber % 100000) / 10000,
            (routingNumber % 10000) / 1000,
            (routingNumber % 1000) / 100,
            (routingNumber % 100) / 10,
            (routingNumber % 10) / 1
        };

        //calculate the route sum using the formula above. 3 7 1.
        int routeSum =
            3 * routingNumberAsArray[0] +
            7 * routingNumberAsArray[1] +
            routingNumberAsArray[2] +
            3 * routingNumberAsArray[3] +
            7 * +routingNumberAsArray[4] +
            routingNumberAsArray[5] +
            3 * routingNumberAsArray[6] +
            7 * routingNumberAsArray[7] +
            routingNumberAsArray[8];
        return routeSum % 10 == 0;
    }
    #endregion Validation Functions

    #region New Additions Added By Johanna for Outbound CitySquares - need to consolidate and discuss with team
    public string FindLeadIDByPhone(string sPhone, string sDNIS)
    {
        string sLeadID = "";
        try
        {

            ExtSQLConn.Open();
            string SQLFindLeadID;
            SqlCommand SqlCmdFindLeadID = new SqlCommand();
            SqlCmdFindLeadID.Connection = ExtSQLConn;
            SQLFindLeadID = "EXEC f_OB_FindLeadIDByPhone '" + sPhone + "','" + sDNIS + "'";
            SqlCmdFindLeadID.CommandText = SQLFindLeadID;
            SqlCmdFindLeadID.CommandType = System.Data.CommandType.Text;
            sLeadID = Convert.ToString(SqlCmdFindLeadID.ExecuteScalar());
            ExtSQLConn.Close();

        }
        catch
        {
            sLeadID = "";
        }
        return sLeadID;
    }
    public void FillLeadData(string sLeadID)
    {
        //Fill Leads Table
        string SQLLeadData = "Select * from " + _CampaignName + "_LeadData (nolock) where LeadID = " + sLeadID + " ";
        SqlCommand SqlCmdLeadData = new SqlCommand(SQLLeadData, SQLConn);
        SqlCmdLeadData.CommandType = System.Data.CommandType.Text;
        SqlDataAdapter dataAdapterLeadData = new SqlDataAdapter(SqlCmdLeadData);
        dataAdapterLeadData.Fill(dtLeadData);
    }

    //FinalizeOBLeadSales() Created on 2/7/2012 by Johanna  
    // Because Sales Disposition are not usually loaded into the dtUsercodes table, we can not use the FinalizeOBLeadNonSales() which checks the dtUsercodes table to determine if the Dispostion is a Final One.
    // All sales dispositions would be final, so we simply update the OBLeads table without.
    public void FinalizeOBLeadSales()
    {
        //finalize lead here
        SQLConn.Open();
        string SQLCallDataUpdate;
        SqlCommand SqlCmdCallDataUpdate = new SqlCommand();
        SqlCmdCallDataUpdate.Connection = SQLConn;

        //SQLCallDataUpdate = "EXEC [65.100.157.132].[SharedPool].[dbo].[f_OB_FinalizeLead] '" + HttpContext.Current.Session["ani"].ToString() + "'";
        SQLCallDataUpdate = "update [65.100.157.132].[SharedPool].[dbo].[OBLeads]" +
                            "set finalized = 1, usercode = '" + dtCallData.Rows[0]["webusercode"].ToString() + "' " +
                            "where LeadID = '" + HttpContext.Current.Session["leadid"].ToString() + "' ";
        SqlCmdCallDataUpdate.CommandText = SQLCallDataUpdate;
        SqlCmdCallDataUpdate.CommandType = System.Data.CommandType.Text;

        SqlCmdCallDataUpdate.ExecuteNonQuery();
        SQLConn.Close();
    }

    public bool IsOBFinalDisposition(string sDisp)
    {
        if (_CampaignName != "Hearthware") // Hearthware is using SF as not final (that is not correct based on Voicnet dispositions)
            return (sDisp == "SC" || sDisp == "SF" || sDisp == "SR" || sDisp == "SS" || sDisp == "SX" || sDisp == "SW");
        else
            return (sDisp != "SF");
    }

    //FinalizeOBLeadNonSales() Created on 2/8/2012 by Johanna
    public void FinalizeOBLeadNonSales()
    {
        foreach (System.Data.DataRow dr in dtUsercodes.Rows) // iterate through usercodes to find matching usercode
        {
            if (dr["usercode"].ToString() == dtCallData.Rows[0]["webusercode"].ToString()) // once match is found, check if it's Final Disposition or not
            {
                SQLConn.Open();
                string SQLCallDataUpdate;
                SqlCommand SqlCmdCallDataUpdate = new SqlCommand();
                SqlCmdCallDataUpdate.Connection = SQLConn;

                if (IsOBFinalDisposition(dr["Disposition"].ToString()))
                {
                    SQLCallDataUpdate = "update [65.100.157.132].[SharedPool].[dbo].[OBLeads]" +
                                        "set finalized = 1, usercode = '" + dtCallData.Rows[0]["webusercode"].ToString() + "' " +
                                        "where LeadID = '" + HttpContext.Current.Session["leadid"].ToString() + "' ";
                }
                else
                {
                    SQLCallDataUpdate = "update [65.100.157.132].[SharedPool].[dbo].[OBLeads]" +
                                        "set usercode = '" + dtCallData.Rows[0]["webusercode"].ToString() + "' " +
                                        "where LeadID = '" + HttpContext.Current.Session["leadid"].ToString() + "' ";
                }
                SqlCmdCallDataUpdate.CommandText = SQLCallDataUpdate;
                SqlCmdCallDataUpdate.CommandType = System.Data.CommandType.Text;

                SqlCmdCallDataUpdate.ExecuteNonQuery();
                SQLConn.Close();

                break;
            }
        }
    }

    public bool FillPreviousCallData(string sLeadID)
    {
        //Fill dtPreviousCallData Table with the previous calldata table where leadid = leadid but enterpriselistid <> listid
        string SQLPreviousCallData = "Select top 1 * from " + _CampaignName + "_CallData (nolock) where LeadID =" + sLeadID + " and enterpriselistid <> " + dtCallData.Rows[0]["enterpriselistid"].ToString() + " order by enterpriselistid desc ";
        SqlCommand SqlCmdPreviousCallData = new SqlCommand(SQLPreviousCallData, SQLConn);
        SqlCmdPreviousCallData.CommandType = System.Data.CommandType.Text;
        SqlDataAdapter dataAdapterPreviousCallData = new SqlDataAdapter(SqlCmdPreviousCallData);
        dataAdapterPreviousCallData.Fill(dtPreviousCallData);

        if (dtPreviousCallData.Rows.Count > 0)
            return true;
        else
            return false;

    }

    public bool CallbackOBLead(string CallBackDateTime, string Phone, string Usercode, string Callback, string DedicatedCallback, string AgentID)
    {
        //string DedCBFlag = "0";
        //if (DedicatedCallback == "true")
        //    DedCBFlag = "1";
        //string CBFlag = "0";
        //if (CBFlag == "true")
        //    CBFlag = "1";
        try
        {
            ExtSQLConn.Open();
            string SQLFinalizeLead;
            SqlCommand SqlCmdFinalizeLead = new SqlCommand();
            SqlCmdFinalizeLead.Connection = ExtSQLConn;
            SQLFinalizeLead = "update [SharedPool].[dbo].[OBLeads] " +
                                            "set CallbackTime = '" + CallBackDateTime + "', Phone = '" + Phone + "', Usercode = '" + Usercode + "', Callback = '" + Callback + "', DedicatedCallback ='" + DedicatedCallback + "', DedicatedAgentID=" + AgentID +
                                            " where LeadID = " + LeadID + " ";
            SqlCmdFinalizeLead.CommandText = SQLFinalizeLead;
            SqlCmdFinalizeLead.CommandType = System.Data.CommandType.Text;
            SqlCmdFinalizeLead.ExecuteNonQuery();
            ExtSQLConn.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }

    #endregion New Additions
}